package com.unidt.www.framework.common.util.file;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.hutool.core.collection.CollectionUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFDataValidationHelper;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.CopyOnWriteArrayList;

@Slf4j
public class FileWithExcelUtil {

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setType(ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setType(ExcelType.XSSF);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<?> list, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(sheetName);
        exportParams.setType(ExcelType.XSSF);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 功能描述: 模板
     * @author sk
     * @date 2022/12/13
     * @param list
     * @param sheetName
     * @param pojoClass
     * @param sheet2Name
     * @param list2
     * @param pojo2Class
     * @param fileName
     * @param response
     * @return void
     */

    public static void exportExcelWithSheet(List<?> list,
                                            String sheetName,
                                            Class<?> pojoClass,
                                            String sheet2Name,
                                            List<?> list2,
                                            Class<?> pojo2Class,
                                            String fileName,
                                            HttpServletResponse response) {
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName(sheetName);
        exportParams.setType(ExcelType.XSSF);
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);

        if(StringUtils.isNotBlank(sheet2Name)){
            ExportParams export2Params = new ExportParams();
            export2Params.setSheetName(sheet2Name);
            export2Params.setType(ExcelType.XSSF);
            new ExcelExportService().createSheet(workbook, export2Params, pojo2Class, list2);
        }
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }

    }



    /**
     * firstRow 开始行号(下标0开始)
     * lastRow  结束行号，最大65535
     * firstCol 区域中第一个单元格的列号 (下标0开始)
     * lastCol 区域中最后一个单元格的列号
     * dataArray 下拉内容
     * sheetHidden 影藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个
     */
    public static void selectList(Workbook workbook, int firstRow, int lastRow, int firstCol, int lastCol, String[] dataArray, int sheetHidden) {
        String hiddenName = "hidden_" + (int) ((Math.random() * 9 + 1) * 100);
        Sheet sheet = workbook.getSheetAt(0);
        Sheet hidden = workbook.createSheet(hiddenName);
        Cell cell = null;
        for (int i = 0, length = dataArray.length; i < length; i++) {
            String name = dataArray[i];
            Row row = hidden.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(name);
        }

        Name namedCell = workbook.createName();
        namedCell.setNameName(hiddenName);
        namedCell.setRefersToFormula(hiddenName + "!$A$1:$A$" + dataArray.length);
        //加载数据,将名称为hidden的
        //DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenName);
        XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, hiddenName + "!$A$1:$A$" + dataArray.length);

        // 设置数据有效性加载在哪一个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);

        DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet);
        DataValidation validation = help.createValidation(constraint, addressList);

        // 将sheet设置为隐藏
        workbook.setSheetHidden(sheetHidden, true);
        sheet.addValidationData(validation);
    }


    public static void exportExcel(List<?> list, String title, String secondTitle, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setSecondTitle(secondTitle);
        exportParams.setType(ExcelType.XSSF);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    public static void exportExcel(List<Map<String, String>> headerList, List<Map<String, Object>> dataList, String title, String sheetName, String fileName, HttpServletResponse response) {
        if (CollectionUtil.isEmpty(headerList)) {
            throw new RuntimeException("标题不能为空");
        }


        //TODO 设置下载文件标题
        List<ExcelExportEntity> excelExportEntityList = new ArrayList<>();
        for (Map<String, String> header :
                headerList) {
            excelExportEntityList.add(new ExcelExportEntity(header.get("headerTitle"), header.get("headerCode"), 15));
        }
        //TODO 初始化下载文件数据
        List<LinkedHashMap<String, Object>> mapList = new ArrayList<>();
        if (CollectionUtil.isNotEmpty(dataList)) {
            for (int index = 0; index < dataList.size(); index++) {
                LinkedHashMap<String, Object> mapInfo = new LinkedHashMap<>();
                for (Map<String, String> headerItem :
                        headerList) {
                    mapInfo.put(headerItem.get("headerCode"), dataList.get(index).get(headerItem.get("headerCode")));
                }
                mapList.add(mapInfo);
            }
        }

        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setType(ExcelType.XSSF);
        downLoadExcel(excelExportEntityList, mapList, fileName, response, exportParams);
    }

    private static void downLoadExcel(List<ExcelExportEntity> excelExportEntityList, List<?> list, String fileName, HttpServletResponse response, ExportParams exportParams) {
        try {
            Workbook workbook = ExcelExportUtil.exportExcel(exportParams, excelExportEntityList, list);
            if (workbook != null) ;

            OutputStream out = response.getOutputStream();
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workbook.write(baos);
            response.setHeader("Content-Length", String.valueOf(baos.size()));
            out.write(baos.toByteArray());
        } catch (IOException e) {
            log.error("[monitor][IO][表单功能]", e);
        }
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            OutputStream out = response.getOutputStream();
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workbook.write(baos);
            response.setHeader("Content-Length", String.valueOf(baos.size()));
            out.write(baos.toByteArray());
        } catch (IOException e) {
            log.error("[monitor][IO][表单功能]", e);
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (StringUtils.isEmpty(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("[monitor][表单功能]", e);
        }
        return list;
    }


    public static List<Map<String, String>> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, List<Map<String, String>> headers) {
        if (file == null) {

            throw new RuntimeException("文件读取失败");
        }
        if (CollectionUtil.isEmpty(headers)) {
            throw new RuntimeException( "标题不能为空");
        }
        List<Map<String, String>> dataList = new CopyOnWriteArrayList<>();
        try {
            Map<String, String> myHeader = new HashMap<>();

            headers.forEach(h -> {
                String headerCode = h.get("headerCode");

                String headerTitle = h.get("headerTitle");

                myHeader.put(headerTitle, headerCode);

            });

            ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);

            List<Map<String, Object>> dataTemp = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, params);

            for (Map row : dataTemp) {

                Map newRow = new HashMap();

                row.forEach((k, v) -> {

                    String code = myHeader.get(k);
                    if (StringUtils.isNotBlank(code)) {
                        if (Objects.nonNull(v)) {
                            String cellValue = String.valueOf(v);
                            if (v instanceof Double) {
                                cellValue = new BigDecimal(String.valueOf(v)).stripTrailingZeros().toPlainString();
                            }
                            newRow.put(code, cellValue);
                        } else {
                            newRow.put(code, null);
                        }

                    }

                });
                dataList.add(newRow);
            }
            return dataList;
        } catch (Exception e) {
            log.error("import file to list map error", e);
        }
        return dataList;
    }


    public static void main(String[] args) {
        String fileName = "任务列表.xlsx";
        String sheetName = "任务列表";
        String title = "任务列表 一月数据";
        List<Map<String, String>> headers = new ArrayList<>();
        Map<String, String> h1 = new HashMap<>();
        h1.put("headerCode", "taskId");
        h1.put("headerTitle", "任务ID");
        Map<String, String> h2 = new HashMap<>();
        h2.put("headerCode", "taskName");
        h2.put("headerTitle", "任务名");
        headers.add(h1);
        headers.add(h2);
        List<Map<String, Object>> data = new ArrayList<>();
        Map<String, Object> d1 = new HashMap<>();
        d1.put("taskId", "12121");
        d1.put("taskName", "任务分配");
        Map<String, Object> d2 = new HashMap<>();
        d2.put("taskId", "12123");
        d2.put("taskName", "任务分配2");
        data.add(d1);
        data.add(d2);
        exportExcel(headers, data, title, sheetName, fileName, null);
    }
}
